#所有的增删查改的基本函数统一写在这，尽可能不要让test.py里面有sql语句

#user
import hashlib

#该函数用于测试
def show_user(cursor):
    cursor.execute("select * from user")

    print(cursor.fetchall())

    return 0


#添加用户
def user_add(cursor,user_name,user_password):

    md=hashlib.md5(user_password.encode())
    
    sql1 = 'INSERT INTO user values(%s,%s,%s)'

    #这个返回值代表目前已有的user的数目
    cursor.execute("select count(user_id) from user")

    #print(cursor.fetchall()[0][0])

    user_id=(cursor.fetchall())[0][0]+1
    
    cursor.execute(sql1,(user_id,user_name,md.hexdigest()))
    
    
    return user_id



#检查是否有同名的用户,返回值True表示存在，False表示不存在
def userCheckSameName(cursor,user_name):
    sql="select * from user where user_name=%s"
    cursor.execute(sql,(user_name))
    res=True
    if len(cursor.fetchall())==0:
        res=False
    return res


#检查密码是否正确,True代表正确，False代表不正确
def userCheckPwd(cursor,user_name,user_pwd):
    sql="select user_passwd from user where user_name=%s"
    cursor.execute(sql,(user_name))
    
    known_pwd=hashlib.md5(user_pwd.encode())
    res=False
    if known_pwd.hexdigest() == cursor.fetchall()[0][0]:
        res=True

    user_id = ""
    
    #如果密码正确
    if res:
        sql="select user_id from user where user_name=%s"
        cursor.execute(sql,(user_name))
        user_id=cursor.fetchall()[0][0]

    return user_id

def getCourses(cursor,title):
    courses=[]
    
    sql = "select course_name,course_teacher,course_dianzan,course_credit,course_id from courses where course_belong_sort = %s;"
    
    cursor.execute(sql,(title))
    
    raw = cursor.fetchall()
    
    #从查询得到的表中获取数据
    for item in raw:
        single_course={}
        single_course["course_name"]=item[0]
        single_course["course_teacher"]=item[1]
        single_course["course_dianzan"]=item[2]
        single_course["course_credit"]=item[3]

        sql = "select tag_content from tag where tag_cid = %s order by tag_dianzan(desc);"
        cursor.execute(sql,(item[4]))
        
        single_course["tag_content"]=cursor.fetchall()[0][0]
        # print(cursor.fetchall()[0][0])
        
        courses.append(single_course)

    return courses